package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.naming.NamingException;
import javax.sql.rowset.CachedRowSet;

import com.sun.rowset.CachedRowSetImpl;

import db.DbConnectionPool;
import db.DbException;
/*import forms.StudentFormDelete;
import forms.StudentFormInsert;
import forms.StudentFormUpdate;*/

public class StudentModel {

    private static String allStudentNamesStr = "SELECT first_name, middle_name, last_name FROM students ";
	private static String applicationStr = "SELECT username, student FROM applicants WHERE username = ?";
	private static String fetchApplicationStr = "SELECT s.pid, decision, first_name, middle_name, last_name, specialization, loc, university, discipline, grad_month, grad_year, gpa, title, country_citizenship, country_residence, street, city, state, zip, tel_code, area_code, num FROM students s, degrees d, address a WHERE s.pid = ? AND a.student = s.pid AND a.student = d.student AND s.pid = d.student ORDER BY pid";
	private static String insertStr = "INSERT INTO students (first_name, middle_name, last_name) VALUES (?, ?, ?)";
	private static String updateStr = "UPDATE students SET first_name = ?, "
			+ "middle_name = ?, last_name = ? WHERE id = ?";
	private static String deleteStr = "DELETE FROM students WHERE id = ?";
	private static String ungradedApplicationStr = "SELECT pid FROM students WHERE numreviews = 0";
	private static String reviewerStr = "SELECT r2.username, r1.grade, r1.content, r1.graded, r1.reviewerid FROM review r1, reviewer r2 WHERE r1.pid = ? AND r1.reviewerid = r2.reviewerid";
	private static String numGradedStr = "SELECT numreviews From students WHERE pid = ?";
    private static String incrementStudentNumReviewersStr = "UPDATE students SET numreviews = (numreviews +1) WHERE pid = ?";
    private static String setDecisionStr = "UPDATE students SET decision = ? WHERE pid = ?";
    private static String disciplineAnalyticsStr = "SELECT a.dname, COUNT(b.discipline) AS \"numApplicants\" FROM disciplines a LEFT OUTER JOIN degrees b ON a.dname = b.discipline GROUP BY a.dname ORDER BY a.dname";
    private static String specializationAnalyticsStr = "SELECT a.sname, Count(b.specialization) AS \"numApplicants\" FROM specializations a LEFT OUTER JOIN students b ON a.sname = b.specialization Group BY a.sname order by a.sname";
    private static String allStudentGradesStr = "SELECT grade FROM review WHERE pid = ? GROUP BY grade";
    
	final static int NULL = 0;
    final static int INTEGER = 4;
    final static int DOUBLE = 8;
    final static int STRING = 12;


    /**
     * getAllStudentNames
     * 
     * gets the names of all the students in the application
     * 
     * @return
     * @throws DbException
     */
    public static CachedRowSet getAllStudentNames() throws DbException {

        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(allStudentNamesStr);

            // Use the created statement to SELECT
            // the applicant attributes FROM the Student table.
            ResultSet applicantInfo = pStmt.executeQuery();

            CachedRowSet crsStudents = new CachedRowSetImpl();
            crsStudents.populate(applicantInfo);

            applicantInfo.close();
            pStmt.close();
            conn.close();

            return crsStudents;
        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
    }
    
	/**
	 * alreadyApplied
	 * 
	 * checks if the given user has already submitted an application
	 * 
	 * @param username   username to be checked for an application
	 * @return  RowSet containing the username and it's application foreingn key
	 * @throws DbException
	 */
	public static CachedRowSet alreadyApplied(String username) throws DbException {

		try {
			Connection conn = DbConnectionPool.getConnection();

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(applicationStr);
	        pStmt.setString(1, username);

			// Use the created statement to SELECT
			// the applicant attributes FROM the Student table.
			ResultSet applicantInfo = pStmt.executeQuery();

			CachedRowSet crsStudents = new CachedRowSetImpl();
			crsStudents.populate(applicantInfo);

			applicantInfo.close();
			pStmt.close();
			conn.close();

			return crsStudents;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (NamingException ex) {
			throw new DbException(ex);
		}
	}

	/**
     * fetchApplication
     * 
     * gets the application of the given pid
     * 
     * @param studentPid   the pid of the student who owns the application
     * @return  RowSet containing the application
     * @throws DbException
     */
    public static CachedRowSet fetchApplication(int studentPid) throws DbException {

        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(fetchApplicationStr);
            pStmt.setInt(1, studentPid);

            // Use the created statement to SELECT
            // the applicant attributes FROM the Student table.
            ResultSet applicantInfo = pStmt.executeQuery();

            CachedRowSet crsApplication = new CachedRowSetImpl();
            crsApplication.populate(applicantInfo);

            applicantInfo.close();
            pStmt.close();
            conn.close();

            return crsApplication;
        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
    }
	
    /**
     * getUngradedApplications
     * returns an arraylist of ungraded applications
     * 
     * @return Arraylist holding all of the ungraded application's Ids
     * @throws DbException 
     */
    public static ArrayList<Integer> getUngradedApplications() throws DbException{
        ArrayList<Integer> applications = new ArrayList<Integer>();
        
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(ungradedApplicationStr);

            // Use the created statement to SELECT
            // the applicant attributes FROM the Student table.
            ResultSet applicantInfo = pStmt.executeQuery();

            CachedRowSet crsApplication = new CachedRowSetImpl();
            crsApplication.populate(applicantInfo);

            while(crsApplication.next()){
                applications.add(crsApplication.getInt("pid"));
            }
            
            applicantInfo.close();
            pStmt.close();
            conn.close();

        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
        
        return applications;
    }
    
    /**
     * getReviewers
     * 
     * gets an arraylist of all the reviewer ids associated with this student
     * 
     * @param   studentid  int containing the id of the student
     * @return  arraylist of reviewer CashedRowSet
     * @throws DbException 
     */
    public static ArrayList<ArrayList> getReviewers(int studentid) throws DbException{
        ArrayList<ArrayList> reviewers = new ArrayList();
        ArrayList currentReviewer;
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(reviewerStr);
            pStmt.setInt(1, studentid);

            // Use the created statement to SELECT
            // the applicant attributes FROM the Student table.
            ResultSet applicantInfo = pStmt.executeQuery();
            ResultSetMetaData rsetMeta = applicantInfo.getMetaData();
            int numCol = rsetMeta.getColumnCount();

            CachedRowSet crsApplication = new CachedRowSetImpl();
            crsApplication.populate(applicantInfo);
            
            
            // loop through all reviewers
            while(crsApplication.next()){
                currentReviewer = new ArrayList();
                // loop through every field in the application
                for(int i = 0; i < numCol; i++){
                    switch(rsetMeta.getColumnType(i+1)){
                    case NULL:
                        break;
                    case INTEGER:
                        currentReviewer.add(crsApplication.getInt(i+1));
                        break;
                    case STRING:
                        currentReviewer.add(crsApplication.getString(i+1));
                        break;
                    default:
                        currentReviewer.add(crsApplication.getString(i+1));
                    }

                }
                reviewers.add(currentReviewer);
            }

            applicantInfo.close();
            pStmt.close();
            conn.close();

        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
        return reviewers;
    }
    
    /**
     * getNumGraded
     * 
     * gets the number of reviewers that have graded this students application
     * 
     * @param studentid   id of the student
     * @return  number of reviewers that have graded the student
     * @throws DbException 
     */
    public static int getNumGraded(int studentid) throws DbException{
        int numGraded = 0;
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(numGradedStr);
            pStmt.setInt(1, studentid);

            // Use the created statement to SELECT
            // the numgraded  FROM the Student table.
            ResultSet applicantInfo = pStmt.executeQuery();

            CachedRowSet crsApplication = new CachedRowSetImpl();
            crsApplication.populate(applicantInfo);
            
            // gets numGraded from the row
            if(crsApplication.next()){
                numGraded = crsApplication.getInt("numreviews");
            }

            applicantInfo.close();
            pStmt.close();
            conn.close();

        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
        
        return numGraded;
    }
    
    /**
     * incrementNumReviewed
     * 
     * increments the number of applications not reviewed by the given delta
     *
     * @param  reviewerid  id of the reviewer that is reviewing the student
     * @param  delta       amount to increment by
     * @return true if successful otherwise false
     * @throws DbException 
     */
    public static boolean incrementNumReviews(int studentid) throws DbException {
        boolean result = false;
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(incrementStudentNumReviewersStr);
            pStmt.setInt(1, studentid);

            // updates the DB
            pStmt.executeUpdate();
            result = true;

            // Commit transaction
            conn.commit();

            pStmt.close();
            conn.close();

        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
        return result;   
    }
    
    /**
     * setDecision
     * 
     * sets the application decision of the given student
     * 
     * @param studentid   id of the student for whom the decision was made
     * @param decision    string containing the decision status
     * @return   true if successfully updated
     * @throws DbException 
     */
    public static boolean setDecision(int studentid, String decision) throws DbException{
        boolean result = false;
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(setDecisionStr);
            pStmt.setString(1, decision);
            pStmt.setInt(2, studentid);

            // updates the DB
            pStmt.executeUpdate();
            result = true;

            // Commit transaction
            conn.commit();

            pStmt.close();
            conn.close();

        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
        return result;  
    }

    
    /**
     * getDisciplineAnalytics
     * 
     * gets a row set of all the disciplines (row 1:dname) and the number of applicants in that discipline (row 2:numApplicants)
     * 
     * @return Cached row set of all the disciplines
     */
    public static CachedRowSet getDisciplineAnalytics() throws DbException {

        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(disciplineAnalyticsStr);

            // Use the created statement to SELECT
            // the disciplines from DISCIPLINES table
            ResultSet disciplines = pStmt.executeQuery();

            CachedRowSet crsApplication = new CachedRowSetImpl();
            crsApplication.populate(disciplines);

            disciplines.close();
            pStmt.close();
            conn.close();

            return crsApplication;
        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
    }
    
    
    /**
     * getSpecializationAnalytics
     * 
     * gets a row set of all the Specializations (row 1:dname) and the number of applicants in that Specialization (row 2:numApplicants)
     * 
     * @return Cached row set of all the Specializations
     */
    public static CachedRowSet getSpecializationAnalytics() throws DbException {

        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(specializationAnalyticsStr);

            // Use the created statement to SELECT
            // the specializations from specializationS table
            ResultSet specializations = pStmt.executeQuery();

            CachedRowSet crsApplication = new CachedRowSetImpl();
            crsApplication.populate(specializations);

            specializations.close();
            pStmt.close();
            conn.close();

            return crsApplication;
        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
    }
    

    /**
     * getAllStudentGrades
     * 
     * get all of the grades associated with the given student
     * 
     * @param studentid id of of the given student
     * @return ArrayList of all of the student's grades
     * @throws DbException 
     */
    public static ArrayList<Integer> getAllStudentGrades(int studentid) throws DbException{
        ArrayList<Integer> grades = new ArrayList<Integer>();
        try {
            Connection conn = DbConnectionPool.getConnection();

            // Create the statement
            PreparedStatement pStmt = conn.prepareStatement(allStudentGradesStr);
            pStmt.setInt(1, studentid);

            // Use the created statement to get students grades
            ResultSet rset = pStmt.executeQuery();

            CachedRowSet crsGrades = new CachedRowSetImpl();
            crsGrades.populate(rset);
            
            // fill array list with grades
            while(crsGrades.next()){
                grades.add(crsGrades.getInt("grade"));
            }

            rset.close();
            pStmt.close();
            conn.close();

            return grades;
        } catch (SQLException ex) {
            throw new DbException(ex);
        } catch (NamingException ex) {
            throw new DbException(ex);
        }
    }
}
